'''
Created on 2011-1-4

@author: Administrator
'''
from util import DB
from util import ip
import chardet



def loadIP2Geo(filepath, db='ip2geo0'):
    """Given ip and location database file path and database name,load into database
       schema:
          ip2geo0(start ip, start_ip_long_value,end_ip,end_ip_long_value,location_name1,location_name_detail)
       
    """
    cur = DB.getCur()
    f = open(filepath, 'r')
    out = open("./tmpip2geosql", 'w')
    t1 = list(f.readline().split())
    print chardet.detect(t1[2])
    print """insert into %s values("%s",'%d',"%s",'%d',"%s","%s")""" % (db, t1[0], ip.ip2long(t1[0]), t1[1], ip.ip2long(t1[1]), t1[2], t1[3])
    out.write("""insert into %s values("%s",'%d',"%s",'%d',"%s","%s")""" % (db, t1[0], ip.ip2long(t1[0]), t1[1], ip.ip2long(t1[1]), t1[2], t1[3]))
    cur.execute("set names 'GBK'")
    cur.execute("truncate %s" % db)    
    cur.execute("""insert into %s values("%s",'%d',"%s",'%d',"%s","%s")""" % (db, t1[0], ip.ip2long(t1[0]), t1[1], ip.ip2long(t1[1]), t1[2], t1[3]))
    
    
    def repl(t):     
        t[2]=t[2].replace("\"","\"\"")
        t[2]=t[2].replace("\'","\'\'")
        t[2]=t[2].replace("\\","\\\\")
        if len(t)<4:
            t.append('-')
        else:
            t[3]=t[3].replace("\"","\"\"")
            t[3]=t[3].replace("\'","\'\'")
            t[3]=t[3].replace("\\","\\\\")     
        #print str(t)
        return t

    for line in f.readlines():
        t = list(line.split())
        t = repl(t)
        try:
            #out.write(""",("%s",'%d',"%s",'%d',"%s","%s")""" % (t[0], ip.ip2long(t[0]), t[1], ip.ip2long(t[1]), t[2], t[3]))
            sql = """insert into %s values("%s",'%d',"%s",'%d',"%s",'%s')""" % (db, t[0], ip.ip2long(t[0]), t[1], ip.ip2long(t[1]), t[2], t[3])
            #print sql
            cur.execute(sql)
        except IndexError:
            print 'index error'
            print str(t)
            continue
        except Exception:
            print sql
            break
    f.close()
    out.close() 


if __name__ == '__main__':
    loadIP2Geo("D:\\downloads\\qqwry_20101230_XiaZaiBa\\qqwry\\sss3.txt")
